﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.IO;
using System.Diagnostics;
using Microsoft.Office.Interop.Excel;
using Workbook = Microsoft.Office.Interop.Excel.Workbook;
using Worksheet = Microsoft.Office.Interop.Excel.Worksheet;
using Spreadsheet = DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml;

namespace ElectronicCommerce.Framework.Excel
{
    public class ExcelHelper : IDisposable
    {
        #region Fields

        private static readonly object _missValue = System.Reflection.Missing.Value;
        private readonly Dictionary<int, Worksheet> sheetTable = new Dictionary<int, Worksheet>();
        private readonly DateTime[] _openSpanTimes = new DateTime[2];
        private readonly string _fileName;
        private Application _excelApp;
        private Workbook _workBook;

        private SpreadsheetDocument _xmlworkbook;

        #endregion

        #region Attributes

        public int WorkSheetCount
        {
            get
            {
                return IsXmlFormat
                    ?
                        _xmlworkbook.WorkbookPart.Workbook.
                        GetFirstChild<Spreadsheet.Sheets>().Count()
                    :
                        _workBook.Sheets.Count;
            }
        }

        private bool IsXmlFormat
        {
            get
            {
                return _xmlworkbook != null;
            }
        }

        #endregion

        #region Contructor

        #region Has file

        public ExcelHelper(string fileName)
        {
            if (!IsExcelFileExist(fileName))
            {
                throw new FileNotFoundException();
            }

            CreateApp();

            _fileName = fileName;

            try
            {
                _workBook = _excelApp.Workbooks._Open(_fileName, _missValue, false, _missValue,
                    _missValue, _missValue, true, _missValue, _missValue, true, _missValue,
                    _missValue, _missValue);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region No file

        public ExcelHelper()
        {
            CreateApp();
            _workBook = _excelApp.Workbooks.Add(_missValue);
        }

        #endregion

        #region OpenXml

        public ExcelHelper(string fileName, bool isCreate)
        {
            if (!isCreate)
            {
                _xmlworkbook = SpreadsheetDocument.Open(fileName, true);
            }
            else
            {
                _xmlworkbook = SpreadsheetDocument.Create(fileName,
                    SpreadsheetDocumentType.Workbook);
                WorkbookPart workbookPart = _xmlworkbook.AddWorkbookPart();
                workbookPart.Workbook = new Spreadsheet.Workbook();
                workbookPart.Workbook.AppendChild<Spreadsheet.Sheets>(new Spreadsheet.Sheets());

                SharedStringTablePart sharestringTablePart = workbookPart.AddNewPart<SharedStringTablePart>();
                sharestringTablePart.SharedStringTable = new SharedStringTable();
                sharestringTablePart.SharedStringTable.UniqueCount = 1;
                sharestringTablePart.SharedStringTable.Count = 1;
                sharestringTablePart.SharedStringTable.Save();
            }
        }

        #endregion

        #endregion

        #region Methods

        #region Sheet

        #region Add

        public void CreateSheets(int count)
        {
            while (count > 0)
            {
                CreateSheet();
                count--;
            }
        }

        public void CreateSheets(string[] sheetNames)
        {
            foreach (var name in sheetNames)
            {
                CreateSheet(name, new string[] { });
            }
        }

        public void CreateSheets(Dictionary<string, string[]> dicSheetInfo)
        {
            foreach (var info in dicSheetInfo)
            {
                CreateSheet(info.Key, info.Value);
            }
        }

        public void CreateSheet()
        {
            CreateSheet(string.Empty, new string[] { });
        }

        public void CreateSheet(string sheetName)
        {
            CreateSheet(sheetName, new string[] { });
        }

        public void CreateSheet(string sheetName, string[] dataList)
        {
            try
            {
                #region Xml

                if (IsXmlFormat)
                {
                    var worksheetPart = _xmlworkbook.WorkbookPart.AddNewPart<WorksheetPart>();
                    worksheetPart.Worksheet = new Spreadsheet.Worksheet(new SheetData());
                    var sheets = _xmlworkbook.WorkbookPart.Workbook.GetFirstChild<Spreadsheet.Sheets>();

                    string relationshipId = _xmlworkbook.WorkbookPart.GetIdOfPart(worksheetPart);

                    // Get a unique ID for the new worksheet.
                    uint sheetId = 1;

                    if (sheets.Elements<Sheet>().Count() > 0)
                    {
                        sheetId = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
                    }

                    // Give the new worksheet a name.
                    sheetName = string.IsNullOrEmpty(sheetName)
                                    ? "Sheet" + sheetId
                                    : sheetName;

                    // Append the new worksheet and associate it with the workbook.
                    var sheet = new Sheet
                    {
                        Id = relationshipId,
                        SheetId = sheetId,
                        Name = sheetName
                    };

                    sheets.Append(sheet);


                    GetWorksheetPart(sheet).Worksheet.Save();

                    if (dataList.Count() > 0)
                    {
                        CreateRow(1, Convert.ToInt32(sheet.SheetId.Value));
                        SetData(Convert.ToInt32(sheet.SheetId.Value), 1, dataList);
                    }
                    return;
                }

                #endregion

                #region Excel

                Worksheet worksheet;
                if (!IsSheetExist(sheetName))
                {
                    worksheet = (Worksheet)_workBook.Sheets
                                                    .Add(_missValue,
                                                         WorkSheetCount == 0
                                                         ?
                                                            _missValue
                                                         :
                                                            GetSheet(WorkSheetCount),
                                                         1, XlSheetType.xlWorksheet);

                    if (!string.IsNullOrEmpty(sheetName))
                    {
                        worksheet.Name = sheetName;
                    }
                }

                if (dataList != null)
                {
                    SetData(sheetName, 1, dataList);
                }

                #endregion
            }
            catch (Exception exception)
            {
                throw exception;
            }
        }

        #endregion

        #region Copy

        public void CopySheet(int index, string newName)
        {
            CreateSheet(newName);
            CopySheet(index, WorkSheetCount, newName);
        }

        public void CopySheet(int index, int subjectIndex)
        {
            CopySheet(index, subjectIndex, string.Empty);
        }

        public void CopySheet(int index, int subjectIndex, string newName)
        {
            try
            {
                CheckArguments(index, 0, 0);
                CheckArguments(subjectIndex, 0, 0);

                var subjectSheet = GetSheet(subjectIndex);
                GetSheet(index).Copy(_missValue, subjectSheet);

                if (string.IsNullOrEmpty(newName))
                {
                    return;
                }

                //重命名
                var worksheet = (Worksheet)subjectSheet.Next; //获取新拷贝的工作表
                worksheet.Name = newName;
            }
            catch (Exception exception)
            {
                throw exception;
            }
        }

        #endregion

        #region Delete

        public void DeleteSheet(string sheetName)
        {
            if (IsXmlFormat)
            {
                DeleteSheet(GetXmlSheet(sheetName));
                return;
            }
            DeleteSheet(GetSheet(sheetName));
        }

        public void DeleteSheet(string[] sheetNames)
        {
            foreach (var name in sheetNames)
            {
                if (IsXmlFormat)
                {
                    DeleteSheet(GetXmlSheet(name));
                    continue;
                }
                DeleteSheet(name);
            }
        }

        public void DeleteSheet(int sheetIndex)
        {
            if (IsXmlFormat)
            {
                DeleteSheet(GetXmlSheet(sheetIndex));
                return;
            }
            DeleteSheet(GetSheet(sheetIndex));
        }

        public void DeleteSheet(int[] sheetIndexs)
        {
            foreach (var index in sheetIndexs)
            {
                if (IsXmlFormat)
                {
                    DeleteSheet(GetXmlSheet(index));
                    continue;
                }
                DeleteSheet(index);
            }
        }

        private void DeleteSheet(_Worksheet sheet)
        {
            try
            {
                CheckArguments(sheet, 0, 0);
                int index = sheet.Index;
                sheet.Delete();
                if (sheetTable.ContainsKey(index))
                {
                    sheetTable.Remove(index);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        private void DeleteSheet(Sheet sheet)
        {
            try
            {
                CheckArguments(sheet, 0, 0);
                var rows = FindRows(sheet);
                DeleteRow(sheet, rows.Select(c => Convert.ToInt32(c.RowIndex.Value)).ToArray(), 1);
                sheet.Remove();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region Update

        public void changeSheetName(string sheetName, string name)
        {
            if (IsXmlFormat)
            {
                changeSheetName(GetXmlSheet(sheetName), name);
                return;
            }
            changeSheetName(GetSheet(sheetName), name);
        }

        public void changeSheetName(int sheetIndex, string name)
        {
            if (IsXmlFormat)
            {
                changeSheetName(GetXmlSheet(sheetIndex), name);
                return;
            }
            changeSheetName(GetSheet(sheetIndex), name);
        }

        private void changeSheetName(_Worksheet sheet, string name)
        {
            CheckArguments(sheet, 0, 0);
            if (string.IsNullOrEmpty(name))
            {
                return;
            }
            sheet.Name = name;
        }

        private void changeSheetName(Sheet sheet, string name)
        {
            CheckArguments(sheet, 0, 0);
            if (string.IsNullOrEmpty(name))
            {
                return;
            }
            sheet.Name = name;
        }

        #endregion

        #endregion

        #region Row

        #region Add

        public void CreateRow(int rowCount, string sheetName)
        {
            if (IsXmlFormat)
            {
                CreateRow(rowCount, GetXmlSheet(sheetName), null);
                return;
            }
            CreateRow(rowCount, GetSheet(sheetName), null);
        }

        public void CreateRow(int rowCount, int sheetIndex)
        {
            if (IsXmlFormat)
            {
                CreateRow(rowCount, GetXmlSheet(sheetIndex), null);
                return;
            }
            CreateRow(rowCount, GetSheet(sheetIndex), null);
        }

        public void CreateRow(int rowCount, int sheetIndex, int? afterIndex)
        {
            if (IsXmlFormat)
            {
                CreateRow(rowCount, GetXmlSheet(sheetIndex), afterIndex);
                return;
            }
            CreateRow(rowCount, GetSheet(sheetIndex), afterIndex);
        }

        public void CreateRow(int rowCount, string sheetName, int? afterIndex)
        {
            if (IsXmlFormat)
            {
                CreateRow(rowCount, GetXmlSheet(sheetName), afterIndex);
                return;
            }
            CreateRow(rowCount, GetSheet(sheetName), afterIndex);
        }

        private void CreateRow(int rowCount, _Worksheet sheet, int? afterIndex)
        {
            try
            {
                CheckArguments(sheet, 0, 0);

                if (afterIndex.HasValue)
                {
                    CheckArguments(sheet, afterIndex.Value, 0);
                }
                else
                {
                    afterIndex = GetSheetRowCount(sheet.Index);
                }

                var range = GetRowRange(afterIndex.Value, sheet.Index);
                for (int i = 0; i < rowCount; i++)
                {
                    range.Insert(XlInsertShiftDirection.xlShiftDown, _missValue);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        private void CreateRow(int rowCount, Sheet sheet, int? afterIndex)
        {
            try
            {
                CheckArguments(sheet, 0, 0);

                if (afterIndex.HasValue)
                {
                    CheckArguments(sheet, afterIndex.Value, 0);
                }
                else
                {
                    afterIndex = GetSheetRowCount(sheet);
                }

                var worksheetPart = GetWorksheetPart(sheet);

                SheetData sheetData = GetSheetData(worksheetPart);

                for (int i = 1; i <= rowCount; i++)
                {
                    var row = new Row
                    {
                        RowIndex = Convert.ToUInt32(afterIndex + i)
                    };
                    sheetData.Append(row);
                }

                ReverseIndex(worksheetPart);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region Delete

        public void DeleteRow(int sheetIndex, int deleteIndex, int deleteCount)
        {
            if (IsXmlFormat)
            {
                DeleteRow(GetXmlSheet(sheetIndex), new[] { deleteIndex }, deleteCount);
                return;
            }
            DeleteRow(GetSheet(sheetIndex), new[] { deleteIndex }, deleteCount);
        }

        public void DeleteRow(string sheetName, int deleteIndex, int deleteCount)
        {
            if (IsXmlFormat)
            {
                DeleteRow(GetXmlSheet(sheetName), new[] { deleteIndex }, deleteCount);
                return;
            }
            DeleteRow(GetSheet(sheetName), new[] { deleteIndex }, deleteCount);
        }

        public void DeleteRow(int sheetIndex, int[] deleteIndexs)
        {
            if (IsXmlFormat)
            {
                DeleteRow(GetXmlSheet(sheetIndex), deleteIndexs, 1);
                return;
            }
            DeleteRow(GetSheet(sheetIndex), deleteIndexs, 1);
        }

        public void DeleteRow(string sheetName, int[] deleteIndexs)
        {
            if (IsXmlFormat)
            {
                DeleteRow(GetXmlSheet(sheetName), deleteIndexs, 1);
                return;
            }
            DeleteRow(GetSheet(sheetName), deleteIndexs, 1);
        }

        private void DeleteRow(_Worksheet sheet, int[] deleteIndexs, int deleteCount)
        {
            try
            {
                if (deleteIndexs.Count() == 1)
                {
                    if (deleteCount == 0)
                    {
                        deleteCount = 1;
                    }
                    for (int i = 1; i <= deleteCount; i++)
                    {
                        deleteIndexs[i] = deleteIndexs[i - 1] + 1;
                    }
                }

                CheckArguments(sheet, deleteIndexs, null);

                Range range;
                for (int i = 0; i < deleteIndexs.Count(); i++)
                {
                    range = GetRowRange(deleteIndexs[i], sheet.Index);
                    range.Delete(_missValue);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        private void DeleteRow(Sheet sheet, int[] deleteIndexs, int deleteCount)
        {
            try
            {
                if (deleteIndexs.Count() == 1)
                {
                    if (deleteCount == 0)
                    {
                        deleteCount = 1;
                    }
                    for (int i = 1; i <= deleteCount; i++)
                    {
                        deleteIndexs[i] = deleteIndexs[i - 1] + 1;
                    }
                }

                CheckArguments(sheet, deleteIndexs, null);

                var worksheetPart = GetWorksheetPart(sheet);

                var deleteRow = new Row[deleteIndexs.Count()];

                for (int i = 0; i < deleteIndexs.Count(); i++)
                {
                    var row = FindRow(worksheetPart, deleteIndexs[i]);
                    row.Remove();
                    deleteRow[i] = row;
                }

                ReverseIndex(worksheetPart);

                ClearShareString(deleteRow);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region Copy

        #region Copy row from index to index

        #region Has subject

        public void CopyRow(int sheetIndex, int copyBeginIndex, int? subjectSheetIndex, int copyCount)
        {
            CopyRow(sheetIndex, subjectSheetIndex, copyBeginIndex, copyCount, -1);
        }

        public void CopyRow(int sheetIndex, int copyBeginIndex, int? subjectSheetIndex, int copyCount, int afterIndex)
        {
            CopyRow(sheetIndex, subjectSheetIndex, copyBeginIndex, copyCount, afterIndex);
        }

        public void CopyRow(int sheetIndex, int? subjectSheetIndex, int copyBeginIndex, int copyCount)
        {
            CopyRow(sheetIndex, null, copyBeginIndex, copyCount, -1);
        }

        #endregion

        #region No subject

        public void CopyRow(int sheetIndex, int copyBeginIndex, int copyCount)
        {
            CopyRow(sheetIndex, null, copyBeginIndex, copyCount, -1);
        }

        public void CopyRow(int sheetIndex, int copyBeginIndex, int copyCount, int afterIndex)
        {
            CopyRow(sheetIndex, null, copyBeginIndex, copyCount, afterIndex);
        }

        #endregion

        public void CopyRow(int sheetIndex, int? subjectSheetIndex, int copyBeginIndex, int copyEndIndex, int? afterIndex)
        {
            CopyBase(sheetIndex, subjectSheetIndex, copyBeginIndex, copyEndIndex, false, afterIndex, true);
        }

        #endregion

        #region Copy row by row count

        #region Has subject

        public void CopyRowByCount(int sheetIndex, int copyBeginIndex, int? subjectSheetIndex, int copyCount)
        {
            CopyRowByCount(sheetIndex, subjectSheetIndex, copyBeginIndex, copyCount, -1);
        }

        public void CopyRowByCount(int sheetIndex, int copyBeginIndex, int? subjectSheetIndex, int copyCount, int afterIndex)
        {
            CopyRowByCount(sheetIndex, subjectSheetIndex, copyBeginIndex, copyCount, afterIndex);
        }

        public void CopyRowByCount(int sheetIndex, int? subjectSheetIndex, int copyBeginIndex, int copyCount)
        {
            CopyRowByCount(sheetIndex, null, copyBeginIndex, copyCount, -1);
        }

        #endregion

        #region No subject

        public void CopyRowByCount(int sheetIndex, int copyBeginIndex, int copyCount)
        {
            CopyRowByCount(sheetIndex, null, copyBeginIndex, copyCount, -1);
        }

        public void CopyRowByCount(int sheetIndex, int copyBeginIndex, int copyCount, int afterIndex)
        {
            CopyRowByCount(sheetIndex, null, copyBeginIndex, copyCount, afterIndex);
        }

        #endregion

        public void CopyRowByCount(int sheetIndex, int? subjectSheetIndex, int copyBeginIndex, int copyCount, int afterIndex)
        {
            CopyBase(sheetIndex, subjectSheetIndex, copyBeginIndex, copyCount, true, afterIndex, true);
        }

        #endregion

        #endregion

        #endregion

        #region Column

        #region Add

        public void CreateCol(int colCount, int sheetIndex)
        {
            if (IsXmlFormat)
            {
                CreateCol(colCount, GetXmlSheet(sheetIndex), null);
                return;
            }
            CreateCol(colCount, GetSheet(sheetIndex), null);
        }

        public void CreateCol(int colCount, string sheetName)
        {
            if (IsXmlFormat)
            {
                CreateCol(colCount, GetXmlSheet(sheetName), null);
                return;
            }
            CreateCol(colCount, GetSheet(sheetName), null);
        }

        public void CreateCol(int colCount, string sheetName, int? afterIndex)
        {
            if (IsXmlFormat)
            {
                CreateCol(colCount, GetXmlSheet(sheetName), afterIndex);
                return;
            }
            CreateCol(colCount, GetSheet(sheetName), afterIndex);
        }

        public void CreateCol(int colCount, int sheetIndex, int? afterIndex)
        {
            if (IsXmlFormat)
            {
                CreateCol(colCount, GetXmlSheet(sheetIndex), afterIndex);
                return;
            }
            CreateCol(colCount, GetSheet(sheetIndex), afterIndex);
        }

        private void CreateCol(int colCount, _Worksheet sheet, int? afterIndex)
        {
            try
            {
                CheckArguments(sheet, 0, 0);

                if (afterIndex.HasValue)
                {
                    CheckArguments(sheet, 0, afterIndex.Value);
                }
                else
                {
                    afterIndex = GetSheetColCount(sheet.Index);
                }

                var range = GetColRange(afterIndex.Value, sheet.Index);
                for (int i = 0; i < colCount; i++)
                {
                    range.Insert(XlInsertShiftDirection.xlShiftToRight, _missValue);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        private void CreateCol(int colCount, Sheet sheet, int? afterIndex)
        {
            try
            {
                CheckArguments(sheet, 0, 0);

                if (afterIndex.HasValue)
                {
                    CheckArguments(sheet, 0, afterIndex.Value);
                }
                else
                {
                    afterIndex = GetSheetColCount(sheet);
                }
                CreateColumnCells(sheet.SheetId, afterIndex.Value + 1, colCount);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region Delete

        public void DeleteCol(string sheetName, int deleteIndex, int deleteCount)
        {
            if (IsXmlFormat)
            {
                DeleteCol(GetXmlSheet(sheetName), new[] { deleteIndex }, deleteCount);
                return;
            }
            DeleteCol(GetSheet(sheetName), new[] { deleteIndex }, deleteCount);
        }

        public void DeleteCol(int sheetIndex, int deleteIndex, int deleteCount)
        {
            if (IsXmlFormat)
            {
                DeleteCol(GetXmlSheet(sheetIndex), new[] { deleteIndex }, deleteCount);
                return;
            }
            DeleteCol(GetSheet(sheetIndex), new[] { deleteIndex }, deleteCount);
        }

        public void DeleteCol(int sheetIndex, int[] deleteIndexs)
        {
            if (IsXmlFormat)
            {
                DeleteCol(GetXmlSheet(sheetIndex), deleteIndexs, 1);
                return;
            }
            DeleteCol(GetSheet(sheetIndex), deleteIndexs, 1);
        }

        public void DeleteCol(string sheetName, int[] deleteIndexs)
        {
            if (IsXmlFormat)
            {
                DeleteCol(GetXmlSheet(sheetName), deleteIndexs, 1);
                return;
            }
            DeleteCol(GetSheet(sheetName), deleteIndexs, 1);
        }

        private void DeleteCol(_Worksheet sheet, int[] deleteIndexs, int deleteCount)
        {
            try
            {
                if (deleteIndexs.Count() == 1)
                {
                    if (deleteCount == 0)
                    {
                        deleteCount = 1;
                    }
                    for (int i = 1; i <= deleteCount; i++)
                    {
                        deleteIndexs[i] = deleteIndexs[i - 1] + 1;
                    }
                }

                CheckArguments(sheet, null, deleteIndexs);

                for (int i = 1; i <= deleteIndexs.Count(); i++)
                {
                    for (int j = 1; j <= deleteCount; j++)
                    {
                        var range = GetColRange(deleteIndexs[0], sheet.Index);
                        range.Delete(_missValue);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        private void DeleteCol(Sheet sheet, int[] deleteIndexs, int deleteCount)
        {
            try
            {
                if (deleteIndexs.Count() == 1)
                {
                    if (deleteCount == 0)
                    {
                        deleteCount = 1;
                    }
                    for (int i = 1; i <= deleteCount; i++)
                    {
                        deleteIndexs[i] = deleteIndexs[i - 1] + 1;
                    }
                }

                CheckArguments(sheet, null, deleteIndexs);

                var cells = new List<Cell>();
                for (int i = 1; i <= deleteIndexs.Count(); i++)
                {
                    for (int j = 1; j <= deleteCount; j++)
                    {
                        var cell = FindCell(sheet, j, deleteIndexs[i]);
                        if (cell != null)
                        {
                            cell.Remove();
                            cells.Add(cell);
                        }
                    }
                }

                foreach (var cellInfo in cells)
                {
                    if (cellInfo.DataType.Value == CellValues.SharedString)
                    {
                        int shareStringId = int.Parse(cellInfo.CellValue.Text);
                        RemoveSharedStringItem(shareStringId);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region Copy

        #region Copy row from index to index

        #region Has subject

        public void CopyCol(int sheetIndex, int copyBeginIndex, int? subjectSheetIndex, int copyCount)
        {
            CopyCol(sheetIndex, subjectSheetIndex, copyBeginIndex, copyCount, -1);
        }

        public void CopyCol(int sheetIndex, int copyBeginIndex, int? subjectSheetIndex, int copyCount, int afterIndex)
        {
            CopyCol(sheetIndex, subjectSheetIndex, copyBeginIndex, copyCount, afterIndex);
        }

        public void CopyCol(int sheetIndex, int? subjectSheetIndex, int copyBeginIndex, int copyCount)
        {
            CopyCol(sheetIndex, null, copyBeginIndex, copyCount, -1);
        }

        #endregion

        #region No subject

        public void CopyCol(int sheetIndex, int copyBeginIndex, int copyCount)
        {
            CopyCol(sheetIndex, null, copyBeginIndex, copyCount, -1);
        }

        public void CopyCol(int sheetIndex, int copyBeginIndex, int copyCount, int afterIndex)
        {
            CopyCol(sheetIndex, null, copyBeginIndex, copyCount, afterIndex);
        }

        #endregion

        public void CopyCol(int sheetIndex, int? subjectSheetIndex, int copyBeginIndex, int copyEndIndex, int? afterIndex)
        {
            CopyBase(sheetIndex, subjectSheetIndex, copyBeginIndex, copyEndIndex, false, afterIndex, false);
        }

        #endregion

        #region Copy row by row count

        #region Has subject

        public void CopyColByCount(int sheetIndex, int copyBeginIndex, int? subjectSheetIndex, int copyCount)
        {
            CopyRowByCount(sheetIndex, subjectSheetIndex, copyBeginIndex, copyCount, -1);
        }

        public void CopyColByCount(int sheetIndex, int copyBeginIndex, int? subjectSheetIndex, int copyCount, int afterIndex)
        {
            CopyColByCount(sheetIndex, subjectSheetIndex, copyBeginIndex, copyCount, afterIndex);
        }

        public void CopyColByCount(int sheetIndex, int? subjectSheetIndex, int copyBeginIndex, int copyCount)
        {
            CopyColByCount(sheetIndex, null, copyBeginIndex, copyCount, -1);
        }

        #endregion

        #region No subject

        public void CopyColByCount(int sheetIndex, int copyBeginIndex, int copyCount)
        {
            CopyColByCount(sheetIndex, null, copyBeginIndex, copyCount, -1);
        }

        public void CopyColByCount(int sheetIndex, int copyBeginIndex, int copyCount, int afterIndex)
        {
            CopyColByCount(sheetIndex, null, copyBeginIndex, copyCount, afterIndex);
        }

        #endregion

        public void CopyColByCount(int sheetIndex, int? subjectSheetIndex, int copyBeginIndex, int copyCount, int afterIndex)
        {
            CopyBase(sheetIndex, subjectSheetIndex, copyBeginIndex, copyCount, true, afterIndex, false);
        }

        #endregion

        #endregion

        #region Update

        public void changeColName(string sheetName, int colIndex, string name)
        {
            if (IsXmlFormat)
            {
                changeColName(GetXmlSheet(sheetName), colIndex, name);
                return;
            }
            changeColName(GetSheet(sheetName), colIndex, name);
        }

        public void changeColName(int sheetIndex, int colIndex, string name)
        {
            if (IsXmlFormat)
            {
                changeColName(GetXmlSheet(sheetIndex), colIndex, name);
                return;
            }
            changeColName(GetSheet(sheetIndex), colIndex, name);
        }

        private void changeColName(_Worksheet sheet, int colIndex, string name)
        {
            CheckArguments(sheet, 0, colIndex);
            if (string.IsNullOrEmpty(name))
            {
                return;
            }
            sheet.Name = name;
        }

        private void changeColName(Sheet sheet, int colIndex, string name)
        {
            CheckArguments(sheet, 0, colIndex);
            if (string.IsNullOrEmpty(name))
            {
                return;
            }
            sheet.Name = name;
        }

        #endregion

        #endregion

        #region Cell data

        #region Set data

        public void SetData(int sheetIndex, int rowIndex, string[] values)
        {
            for (int i = 0; i < values.Count(); i++)
            {
                if (IsXmlFormat)
                {
                    SetData(GetXmlSheet(sheetIndex), rowIndex, i + 1, values[i]);
                }
                else
                {
                    SetData(GetSheet(sheetIndex), rowIndex, i + 1, values[i]);
                }
            }
        }

        public void SetData(string sheetName, int rowIndex, string[] values)
        {
            for (int i = 0; i < values.Count(); i++)
            {
                if (IsXmlFormat)
                {
                    SetData(GetXmlSheet(sheetName), rowIndex, i + 1, values[i]);
                }
                else
                {
                    SetData(GetSheet(sheetName), rowIndex, i + 1, values[i]);
                }
            }
        }

        public void SetData(int sheetIndex, string[] values, int colIndex)
        {
            for (int i = 0; i < values.Count(); i++)
            {
                if (IsXmlFormat)
                {
                    SetData(GetXmlSheet(sheetIndex), i + 1, colIndex, values[i]);
                }
                else
                {
                    SetData(GetSheet(sheetIndex), i + 1, colIndex, values[i]);
                }
            }
        }

        public void SetData(string sheetName, string[] values, int colIndex)
        {
            for (int i = 0; i < values.Count(); i++)
            {
                if (IsXmlFormat)
                {
                    SetData(GetXmlSheet(sheetName), i + 1, colIndex, values[i]);
                }
                else
                {
                    SetData(GetSheet(sheetName), i + 1, colIndex, values[i]);
                }
            }
        }

        public void SetData(string sheetName, int rowIndex, int colIndex, string value)
        {
            if (IsXmlFormat)
            {
                SetData(GetXmlSheet(sheetName), rowIndex, colIndex, value);
                return;
            }
            SetData(GetSheet(sheetName), rowIndex, colIndex, value);
        }

        public void SetData(int sheetIndex, int rowIndex, int colIndex, string value)
        {
            if (IsXmlFormat)
            {
                SetData(GetXmlSheet(sheetIndex), rowIndex, colIndex, value);
                return;
            }
            SetData(GetSheet(sheetIndex), rowIndex, colIndex, value);
        }

        private void SetData(_Worksheet sheet, int rowIndex, int colIndex, string value)
        {
            try
            {
                CheckArguments(sheet, rowIndex, colIndex);
                ((Range)sheet.Cells[rowIndex, colIndex]).ClearContents();
                sheet.Cells[rowIndex, colIndex] = value;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        private void SetData(Sheet sheet, int rowIndex, int colIndex, string value)
        {
            try
            {
                CreateCell(sheet, rowIndex, colIndex);

                CheckArguments(sheet, rowIndex, colIndex);

                var cells = FindRow(sheet, rowIndex).Elements<Cell>().
                    Where(c => c.CellReference.Value
                        == ConvertToCellId(colIndex, rowIndex));
                cells.FirstOrDefault().CellValue = new CellValue(value);
                cells.FirstOrDefault().DataType = new EnumValue<CellValues>(CellValues.SharedString);
                GetWorksheetPart(sheet).Worksheet.Save();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region Get data

        public object[] GetRowData(string sheetName, int rowIndex)
        {
            return GetData(sheetName, rowIndex, CellTypeEnum.Row, GetRowCellCount(sheetName, rowIndex));
        }

        public object[] GetRowData(int sheetIndex, int rowIndex)
        {
            return GetData(sheetIndex, rowIndex, CellTypeEnum.Row, GetRowCellCount(sheetIndex, rowIndex));
        }

        public object[] GetColumnData(string sheetName, int rowIndex)
        {
            return GetData(sheetName, rowIndex, CellTypeEnum.Col, GetSheetRowCount(sheetName));
        }

        public object[] GetColumnData(int sheetIndex, int rowIndex)
        {
            return GetData(sheetIndex, rowIndex, CellTypeEnum.Col, GetSheetRowCount(sheetIndex));
        }

        public object[] GetData(int sheetIndex, int index, CellTypeEnum type, int count)
        {
            switch (type)
            {
                case CellTypeEnum.Row:
                    if (IsXmlFormat)
                    {
                        return GetData(GetXmlSheet(sheetIndex), index, 0, null, count);
                    }
                    return GetData(GetSheet(sheetIndex), index, 0, null, count);
                case CellTypeEnum.Col:
                    if (IsXmlFormat)
                    {
                        return GetData(GetXmlSheet(sheetIndex), 0, index, null, count);
                    }
                    return GetData(GetSheet(sheetIndex), 0, index, null, count);
                default:
                    return new object[] { };
            }
        }

        public object[] GetData(string name, int index, CellTypeEnum type, int count)
        {
            switch (type)
            {
                case CellTypeEnum.Row:
                    if (IsXmlFormat)
                    {
                        return GetData(GetXmlSheet(name), index, 0, null, count);
                    }
                    return GetData(GetSheet(name), index, 0, null, count);
                case CellTypeEnum.Col:
                    if (IsXmlFormat)
                    {
                        return GetData(GetXmlSheet(name), 0, index, null, count);
                    }
                    return GetData(GetSheet(name), 0, index, null, count);
                default:
                    return new object[] { };
            }
        }

        public object[] GetData(string name, int index, CellTypeEnum type, int startIndex, int count)
        {
            switch (type)
            {
                case CellTypeEnum.Row:
                    if (IsXmlFormat)
                    {
                        return GetData(GetXmlSheet(name), index, 0, startIndex, count);
                    }
                    return GetData(GetSheet(name), index, 0, startIndex, count);
                case CellTypeEnum.Col:
                    if (IsXmlFormat)
                    {
                        return GetData(GetXmlSheet(name), 0, index, startIndex, count);
                    }
                    return GetData(GetSheet(name), 0, index, startIndex, count);
                default:
                    return new object[] { };
            }
        }

        public object GetData(string sheetName, int rowIndex, int colIndex)
        {
            if (IsXmlFormat)
            {
                return GetData(GetXmlSheet(sheetName), rowIndex, colIndex, null, 0)[0];
            }
            return GetData(GetSheet(sheetName), rowIndex, colIndex, null, 0)[0];
        }

        public object GetData(int sheetIndex, int rowIndex, int colIndex)
        {
            if (IsXmlFormat)
            {
                return GetData(GetXmlSheet(sheetIndex), rowIndex, colIndex, null, 0)[0];
            }
            return GetData(GetSheet(sheetIndex), rowIndex, colIndex, null, 0)[0];
        }

        private object[] GetData(_Worksheet sheet, int rowIndex, int colIndex, int? startIndex, int count)
        {
            try
            {
                if (!startIndex.HasValue)
                {
                    startIndex = 1;
                }
                string[] values;
                if (rowIndex != 0 && colIndex == 0)
                {
                    CheckArguments(sheet, rowIndex, startIndex.Value + count, false);

                    values = new string[count];
                    for (int j = startIndex.Value; j <= count; j++)
                    {
                        values[j - startIndex.Value] =
                            Convert.ToString(((Range)sheet.Cells[rowIndex, j]).Text);
                    }
                }
                else if (rowIndex == 0 && colIndex != 0)
                {
                    CheckArguments(sheet, startIndex.Value + count, rowIndex, false);

                    values = new string[count];
                    for (int i = startIndex.Value; i <= count; i++)
                    {
                        values[i - startIndex.Value] =
                            Convert.ToString(((Range)sheet.Cells[i, colIndex]).Text);
                    }
                }
                else
                {
                    CheckArguments(sheet, rowIndex, colIndex, false);
                    values = new[] { Convert.ToString(((Range)sheet.Cells[rowIndex, colIndex]).Text) };
                }
                return values;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        private object[] GetData(Sheet sheet, int rowIndex, int colIndex, int? startIndex, int count)
        {
            try
            {
                if (!startIndex.HasValue)
                {
                    startIndex = 1;
                }
                string[] values;
                Cell cellTemp;
                if (rowIndex != 0 && colIndex == 0)
                {
                    CheckArguments(sheet, rowIndex, startIndex.Value + count, false);
                    values = new string[count];
                    for (int j = startIndex.Value; j <= count; j++)
                    {
                        cellTemp = FindCell(sheet, rowIndex, j);
                        values[j - startIndex.Value] = cellTemp == null
                                                           ? string.Empty
                                                           : cellTemp.CellValue.Text;
                    }
                }
                else if (rowIndex == 0 && colIndex != 0)
                {
                    CheckArguments(sheet, startIndex.Value + count, rowIndex, false);

                    values = new string[count];
                    for (int i = startIndex.Value; i <= count; i++)
                    {
                        cellTemp = FindCell(sheet, i, colIndex);
                        values[i - startIndex.Value] = cellTemp == null
                                                           ? string.Empty
                                                           : cellTemp.CellValue.Text;
                    }
                }
                else
                {
                    CheckArguments(sheet, rowIndex, colIndex, false);
                    cellTemp = FindCell(sheet, rowIndex, colIndex);
                    values = new[]
                                 {
                                     cellTemp == null
                                         ? string.Empty
                                         : cellTemp.CellValue.Text
                                 };
                }
                return values;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region Create xml cell

        private void CreateColumnCells(int sheetIndex, int columnIndex, int columnCount)
        {
            for (int i = 1; i <= columnCount; i++)
            {
                CreateCell(GetXmlSheet(sheetIndex), i, columnIndex);
            }
        }

        private void CreateColumnCells(string sheetName, int columnIndex, int columnCount)
        {
            for (int i = 1; i <= columnCount; i++)
            {
                CreateCell(GetXmlSheet(sheetName), i, columnIndex);
            }
        }

        private void CreateRowCells(int sheetIndex, int rowIndex, int columnCount)
        {
            for (int i = 1; i <= columnCount; i++)
            {
                CreateCell(GetXmlSheet(sheetIndex), rowIndex, i);
            }
        }

        private void CreateRowCells(string sheetName, int rowIndex, int columnCount)
        {
            for (int i = 1; i <= columnCount; i++)
            {
                CreateCell(GetXmlSheet(sheetName), rowIndex, i);
            }
        }

        private void CreateCell(string sheetName, int rowIndex, int columnIndex)
        {
            CreateCell(GetXmlSheet(sheetName), rowIndex, columnIndex);
        }

        private void CreateCell(int sheetIndex, int rowIndex, int columnIndex)
        {
            CreateCell(GetXmlSheet(sheetIndex), rowIndex, columnIndex);
        }

        private void CreateCell(Sheet sheet, int rowIndex, int columnIndex)
        {
            CheckArguments(sheet, rowIndex, columnIndex);
            var worksheetPart = GetWorksheetPart(sheet);
            Row row = FindRow(worksheetPart, rowIndex);

            string refIdStr = ConvertToCellId(columnIndex, rowIndex);

            var rows = row.Elements<Cell>();

            // If there is not a cell with the specified column name, insert one.  
            if (rows.Where(c => c.CellReference.Value == refIdStr).Count() == 0)
            {
                #region Create cell

                Cell refCell = null;

                foreach (Cell cell in row.Elements<Cell>())
                {
                    if (string.Compare(cell.CellReference.Value, refIdStr, true) > 0)
                    {
                        refCell = cell;
                        break;
                    }
                }

                var newCell = new Cell { CellReference = refIdStr };

                row.InsertBefore(newCell, refCell);

                #endregion
            }
        }

        #endregion

        #endregion

        #region Merge

        private void MergeCells(_Worksheet sheet, int beginRowIndex, int beginColumnIndex, int endRowIndex, int endColumnIndex, string text)
        {

            var range = GetRange(new[] { beginRowIndex, endRowIndex },
                            new[] { beginColumnIndex, endColumnIndex }, sheet);


            range.ClearContents();
            range.MergeCells = true;
            range.Value2 = text;
            range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
            range.VerticalAlignment = XlVAlign.xlVAlignCenter;
        }

        #endregion

        #region Save

        public void SaveXml()
        {
            Save(string.Empty, string.Empty, OutputTypeEnum.Xml);
        }

        public void Save()
        {
            Save(string.Empty, string.Empty, OutputTypeEnum.Default);
        }

        public void Save(string file)
        {
            Save(file, string.Empty, OutputTypeEnum.Default);
        }

        public void Save(string path, OutputTypeEnum type)
        {
            Save(path, string.Empty, type);
        }

        public void Save(string path, string fileName, OutputTypeEnum type)
        {
            try
            {
                #region Xml

                if (type == OutputTypeEnum.Xml && IsXmlFormat)
                {
                    var sheets = _xmlworkbook.WorkbookPart.Workbook.Descendants<Sheet>();
                    if (sheets.Count() > 0)
                    {
                        GetWorksheetPart(sheets.First()).Worksheet.Save();
                        _xmlworkbook.WorkbookPart.Workbook.Save();
                        return;
                    }
                    throw new FileNotFoundException();
                }

                #endregion

                #region Excel

                if (type == OutputTypeEnum.Default)
                {
                    if (string.IsNullOrEmpty(path))
                    {
                        _workBook.Save();
                    }
                    else
                    {
                        _workBook.SaveCopyAs(path);
                    }
                }
                else
                {
                    XlFileFormat? format;
                    switch (type)
                    {
                        case OutputTypeEnum.Xml:
                            format = XlFileFormat.xlXMLSpreadsheet;
                            if (string.IsNullOrEmpty(fileName))
                            {
                                fileName = DateTime.Now.ToString("yyyy_MM_dd_HH_mm_ss") + ".xml";
                            }
                            break;
                        case OutputTypeEnum.CSV:
                            format = XlFileFormat.xlCSV;
                            if (string.IsNullOrEmpty(fileName))
                            {
                                fileName = DateTime.Now.ToString("yyyy_MM_dd_HH_mm_ss") + ".csv";
                            }
                            break;
                        case OutputTypeEnum.Html:
                            format = XlFileFormat.xlHtml;
                            if (string.IsNullOrEmpty(fileName))
                            {
                                fileName = DateTime.Now.ToString("yyyy_MM_dd_HH_mm_ss") + ".html";
                            }
                            break;
                        default:
                            format = XlFileFormat.xlWorkbookNormal;
                            if (string.IsNullOrEmpty(fileName))
                            {
                                fileName = DateTime.Now.ToString("yyyy_MM_dd_HH_mm_ss") + ".xls";
                            }
                            break;
                    }

                    string fullPath = path.TrimEnd('\\') + "\\" + fileName;

                    _workBook.SaveAs(fullPath,
                                     format.Value,
                                     _missValue, _missValue, _missValue, _missValue,
                                     XlSaveAsAccessMode.xlExclusive,
                                     _missValue, _missValue, _missValue, _missValue,
                                     _missValue);
                }

                #endregion
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #endregion

        #region Private Methods

        #region Create application

        private void CreateApp()
        {
            _openSpanTimes[0] = DateTime.Now;
            _excelApp = new ApplicationClass
            {
                Visible = false,
                UserControl = true,
                DisplayAlerts = false
            };
            _openSpanTimes[1] = DateTime.Now;
        }

        #endregion

        #region Dispose

        ~ExcelHelper()
        {
            Dispose(false);
        }

        public void Dispose()
        {
            Dispose(true);

            GC.SuppressFinalize(this);
        }

        protected virtual void Dispose(bool disposing)
        {
            if (!disposing)
            {
                return;
            }

            #region Object set null

            if (_xmlworkbook != null)
            {
                _xmlworkbook.Close();
                _xmlworkbook.Dispose();
            }

            if (_workBook != null)
            {
                _workBook.Close(null, null, null);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(_workBook);
                _workBook = null;
            }
            if (_excelApp != null)
            {
                _excelApp.Workbooks.Close();
                if (_excelApp.Workbooks != null)
                {
                    _excelApp.Workbooks.Close();
                }
                _excelApp.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(_excelApp);
                _excelApp = null;
            }

            GC.Collect();

            #endregion

            #region Kill porcess

            if (!IsXmlFormat)
            {
                Process.GetProcessesByName("Excel").Where(
                    c => c.StartTime > _openSpanTimes[0] && c.StartTime < _openSpanTimes[1])
                    .ToList().ForEach(c => c.Kill());
            }

            #endregion
        }

        #endregion

        #region Find sheet

        #region Find excel sheet

        private Worksheet GetSheet(int index)
        {
            if (sheetTable.ContainsKey(index))
            {
                return sheetTable[index];
            }
            var sheetTemp = _workBook.Sheets.get_Item(index) as Worksheet;
            if (sheetTemp != null && !sheetTable.ContainsKey(sheetTemp.Index))
            {
                sheetTable.Add(sheetTemp.Index, sheetTemp);
            }
            return sheetTemp;
        }

        private Worksheet GetSheet(string sheetName)
        {
            var temp = sheetTable.Where(c => c.Value.Name == sheetName);
            if (temp != null && temp.Count() >= 1)
            {
                return temp.FirstOrDefault().Value;
            }
            for (int i = 1; i <= _workBook.Sheets.Count; i++)
            {
                var sheetTemp = (Worksheet)_workBook.Sheets[i];
                if (sheetTemp.Name.Equals(sheetName))
                {
                    sheetTable.Add(sheetTemp.Index, sheetTemp);
                    return sheetTemp;
                }
            }
            return null;
        }

        #endregion

        #region Find xml sheet

        private Sheet GetXmlSheet(int index)
        {
            if (index == 0)
            {
                return null;
            }
            var rtv = _xmlworkbook.WorkbookPart.Workbook.Descendants<Sheet>()
                 .Where(s => s.SheetId == index);
            if (rtv.Count() == 0)
            {
                return null;
            }
            return rtv.FirstOrDefault();
        }

        private Sheet GetXmlSheet(string sheetName)
        {
            var rtv = _xmlworkbook.WorkbookPart.Workbook.Descendants<Sheet>()
                .Where(s => s.Name == sheetName);
            if (rtv.Count() == 0)
            {
                return null;
            }
            return rtv.FirstOrDefault();
        }

        #endregion

        #endregion

        #region Find range

        #region Excel

        #region Get column range

        private Range GetColRange(int colIndex, int sheetIndex)
        {
            var sheet = GetSheet(sheetIndex);
            CheckArguments(sheet, 0, colIndex);
            return sheet.Columns[_missValue, colIndex] as Range;
        }

        private Range GetColsRange(int startColIndex, int endColIndex, int sheetIndex)
        {
            var cellOne = new int[2];
            cellOne[0] = 1;
            cellOne[1] = startColIndex;
            var cellTwo = new int[2];
            cellTwo[0] = GetSheetRowCount(sheetIndex);
            cellTwo[1] = endColIndex;
            return GetRange(cellOne, cellTwo, sheetIndex);
        }

        #endregion

        #region Get row range

        private Range GetRowRange(int rowIndex, int sheetIndex)
        {
            var sheet = GetSheet(sheetIndex);
            CheckArguments(sheet, rowIndex, 0);
            return sheet.Rows[rowIndex, _missValue] as Range;
        }

        private Range GetRowsRange(int startRowIndex, int endRowIndex, int sheetIndex)
        {
            var cellOne = new int[2];
            cellOne[0] = startRowIndex;
            cellOne[1] = 1;
            var cellTwo = new int[2];
            cellTwo[0] = endRowIndex;
            cellTwo[1] = GetSheetColCount(sheetIndex);
            return GetRange(cellOne, cellTwo, sheetIndex);
        }

        #endregion

        #region Get self-defined range

        private Range GetRange(int[] cellOne, int[] cellTwo, int sheetIndex)
        {
            return GetRange(cellOne, cellTwo, GetSheet(sheetIndex));
        }

        private Range GetRange(int[] cellOne, int[] cellTwo, string sheetName)
        {
            return GetRange(cellOne, cellTwo, GetSheet(sheetName));
        }

        private Range GetRange(int[] cellOne, int[] cellTwo, _Worksheet sheet)
        {
            CheckArguments(sheet,
                new[] { cellOne[0], cellTwo[0] },
                new[] { cellOne[1], cellTwo[1] });

            return sheet.get_Range(
                sheet.Cells[
                    cellOne[0] == 0 ? _missValue : cellOne[0],
                    cellOne[1] == 0 ? _missValue : cellOne[1]
                    ],
                sheet.Cells[
                    cellTwo[0] == 0 ? _missValue : cellTwo[0],
                    cellTwo[1] == 0 ? _missValue : cellTwo[1]
                    ]);
        }

        #endregion

        #endregion

        #region Xml

        #region Xml find row

        private Row FindRow(int sheetIndex)
        {
            return FindRow(GetWorksheetPart(sheetIndex), 0);
        }

        private Row FindRow(int sheetIndex, int rowIndex)
        {
            return FindRow(GetWorksheetPart(sheetIndex), rowIndex);
        }

        private Row FindRow(string sheetName, int rowIndex)
        {
            return FindRow(GetWorksheetPart(sheetName), rowIndex);
        }

        private Row FindRow(Sheet sheet, int rowIndex)
        {
            return FindRow(GetWorksheetPart(sheet), rowIndex);
        }

        private Row FindRow(WorksheetPart worksheetPart, int rowIndex)
        {
            var rows = FindRows(worksheetPart);
            if (rows != null && rows.Count() > 0)
            {
                rows = rows.Where(s => s.RowIndex == rowIndex);
            }
            return rows == null || rows.Count() == 0 ? null : rows.First();
        }

        private IEnumerable<Row> FindRows(Sheet sheet)
        {
            return FindRows(GetWorksheetPart(sheet));
        }

        private IEnumerable<Row> FindRows(WorksheetPart worksheetPart)
        {
            SheetData sheetData = GetSheetData(worksheetPart);
            var allRows = sheetData.Elements<Row>();
            return allRows;
        }

        #endregion

        #region Xml find cell

        private Cell FindCell(string sheetName, int rowIndex, int columnIndex)
        {
            return FindCell(GetXmlSheet(sheetName), rowIndex, columnIndex);
        }

        private Cell FindCell(int sheetIndex, int rowIndex, int columnIndex)
        {
            return FindCell(GetXmlSheet(sheetIndex), rowIndex, columnIndex);
        }

        private Cell FindCell(Sheet sheet, int rowIndex, int columnIndex)
        {
            var worksheetPart = GetWorksheetPart(sheet);

            Row row = FindRow(worksheetPart, rowIndex);

            string refIdStr = ConvertToCellId(columnIndex, rowIndex);

            var rows = row.Elements<Cell>();

            var rtvRows = rows.Where(c => c.CellReference.Value == refIdStr);

            return rtvRows.Count() == 0 ? null : rtvRows.First();
        }

        #endregion

        #endregion

        #endregion

        #region Get count

        #region Row count

        public int GetSheetRowCount(string sheetName)
        {
            if (IsXmlFormat)
            {
                return GetSheetRowCount(GetWorksheetPart(sheetName));
            }
            return GetSheetRowCount(GetSheet(sheetName));
        }

        public int GetSheetRowCount(int sheetIndex)
        {
            if (IsXmlFormat)
            {
                return GetSheetRowCount(GetWorksheetPart(sheetIndex));
            }
            return GetSheetRowCount(sheetIndex);
        }

        private int GetSheetRowCount(_Worksheet sheet)
        {
            if (sheet == null)
            {
                return 0;
            }
            return sheet.Rows.Count;
        }

        private int GetSheetRowCount(Sheet sheet)
        {
            return GetSheetRowCount(GetWorksheetPart(sheet));
        }

        private int GetSheetRowCount(WorksheetPart worksheetPart)
        {
            if (worksheetPart == null)
            {
                return 0;
            }
            return worksheetPart.Worksheet.Descendants<Row>().Count();
        }

        #endregion

        #region Column count

        public int GetSheetColCount(string sheetName)
        {
            if (IsXmlFormat)
            {
                return GetSheetColCount(GetWorksheetPart(sheetName));
            }
            return GetSheetColCount(sheetName);
        }

        public int GetSheetColCount(int sheetIndex)
        {
            if (IsXmlFormat)
            {
                return GetSheetColCount(GetWorksheetPart(sheetIndex));
            }
            return GetSheetColCount(sheetIndex);
        }

        private int GetSheetColCount(_Worksheet sheet)
        {
            if (sheet == null)
            {
                return 0;
            }
            return sheet.Columns.Count;
        }

        private int GetSheetColCount(Sheet sheet)
        {
            return GetSheetColCount(GetWorksheetPart(sheet));
        }

        private static int GetSheetColCount(WorksheetPart worksheetPart)
        {
            if (worksheetPart == null)
            {
                return 0;
            }
            return worksheetPart.Worksheet.Descendants<Column>().Count();
        }

        #endregion

        #region Xml cell count

        public int GetRowCellCount(string sheetName, int rowIndex)
        {
            return GetRowCellCount(GetWorksheetPart(sheetName), rowIndex);
        }

        public int GetRowCellCount(int sheetIndex, int rowIndex)
        {
            return GetRowCellCount(GetWorksheetPart(sheetIndex), rowIndex);
        }

        private int GetRowCellCount(Sheet sheet, int rowIndex)
        {
            return GetRowCellCount(GetWorksheetPart(sheet), rowIndex);
        }

        private int GetRowCellCount(WorksheetPart worksheetPart, int rowIndex)
        {
            if (worksheetPart == null)
            {
                return 0;
            }

            Row row = FindRow(worksheetPart, rowIndex);

            var rows = row.Elements<Cell>();

            return rows == null ? 0 : rows.Count();
        }

        #endregion

        #endregion

        #region Copy base

        private void CopyBase(int sheetIndex, int? subjectSheetIndex, int copyBeginIndex, int copyEndIndex, bool isSelectCount, int? afterIndex, bool isRow)
        {
            try
            {
                #region Check

                if (isRow)
                {
                    CheckArguments(sheetIndex,
                                   new[]
                                       {
                                           copyBeginIndex, isSelectCount
                                                               ?
                                                                   copyEndIndex
                                                               :
                                                                   0
                                       },
                                   null);
                }
                else
                {
                    CheckArguments(sheetIndex,
                                   null,
                                   new[]
                                       {
                                           copyBeginIndex, isSelectCount
                                                               ?
                                                                   copyEndIndex
                                                               :
                                                                   0
                                       });
                }

                //if (copyBeginIndex > copyEndIndex)
                //{
                //    throw new Exception("bengin index over end index");

                //}

                int subjectIndex = subjectSheetIndex.HasValue
                                       ? subjectSheetIndex.Value : sheetIndex;

                if (afterIndex.HasValue)
                {
                    if (isRow)
                    {
                        CheckArguments(subjectIndex, afterIndex.Value, 0);
                    }
                    else
                    {
                        CheckArguments(subjectIndex, 0, afterIndex.Value);
                    }
                }

                #endregion

                #region Base info

                int totalCount = isSelectCount ? copyEndIndex : copyBeginIndex - copyEndIndex;

                int tempCount = isRow
                                    ? GetSheet(subjectIndex).Rows.Count
                                    :
                                        GetSheet(subjectIndex).Columns.Count;

                afterIndex = afterIndex.HasValue
                                 ?
                                     afterIndex
                                 :
                                     tempCount;

                int maxCount = isRow
                                   ?
                                       GetSheetRowCount(subjectIndex)
                                   :
                                       GetSheetColCount(subjectIndex);

                int overdueCount = subjectIndex + totalCount;

                #endregion

                #region add

                if (overdueCount > maxCount)
                {
                    if (isRow)
                    {
                        CreateCol(overdueCount, subjectIndex);
                    }
                    else
                    {
                        CreateRow(overdueCount, subjectIndex);
                    }
                }

                #endregion

                #region Copy

                for (int i = afterIndex.Value; i < totalCount; i++, copyBeginIndex++)
                {
                    var range = isRow
                                 ?
                                     GetRowRange(copyBeginIndex, sheetIndex)
                                 :
                                     GetColRange(copyBeginIndex, sheetIndex);
                    var rangeTemp = isRow
                                        ?
                                            GetRowRange(i, subjectIndex)
                                        :
                                            GetColRange(i, subjectIndex);
                    rangeTemp.Copy(range);
                }

                #endregion

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region Xml helper

        #region Get sheet data form part

        private SheetData GetSheetData(WorksheetPart worksheetPart)
        {
            if (_xmlworkbook.WorkbookPart == null)
            {
                _xmlworkbook.AddWorkbookPart();
            }
            if (worksheetPart == null)
            {
                worksheetPart = _xmlworkbook.WorkbookPart.AddNewPart<WorksheetPart>();
            }
            if (worksheetPart.Worksheet == null)
            {
                worksheetPart.Worksheet = new Spreadsheet.Worksheet();
            }
            if (worksheetPart.Worksheet.GetFirstChild<SheetData>() == null)
            {
                worksheetPart.Worksheet.AppendChild(new SheetData());
            }
            return worksheetPart.Worksheet.GetFirstChild<SheetData>();
        }

        #endregion

        #region ReverseIndex

        private void ReverseIndex(WorksheetPart worksheetPart)
        {
            var rows = worksheetPart.Worksheet.Descendants<Row>();
            if (rows.Count() == 0)
            {
                return;
            }
            int i = 1;
            rows.ToList().ForEach(delegate(Row row)
            {
                foreach (Cell cell in row.Elements<Cell>())
                {
                    // Update the references for reserved cells.
                    cell.CellReference = new StringValue(
                            cell.CellReference.Value.Replace(
                              row.RowIndex.Value.ToString(),
                              i.ToString()
                            )
                          );
                }

                // Update the reserved row index.
                row.RowIndex = Convert.ToUInt32(i);
                i++;
            });
        }

        #endregion

        #region Clean up the shared string table.

        private void ClearShareString(IEnumerable<Row> rows)
        {
            if (rows == null || rows.Count() == 0)
            {
                return;
            }
            rows.ToList().ForEach(delegate(Row row)
            {
                foreach (
                    int shareStringId in
                        row.Elements<Cell>().Where(
                            c => c.DataType.Value ==
                                CellValues.SharedString).Select(
                                  r => int.Parse(r.CellValue.Text)))
                {
                    RemoveSharedStringItem(shareStringId);
                }
            });
        }

        private void RemoveSharedStringItem(int shareStringId)
        {
            bool remove = true;

            #region Check items is reference by other item

            foreach (var workpart in _xmlworkbook.WorkbookPart.GetPartsOfType<WorksheetPart>())
            {
                foreach (var cell in GetSheetData(workpart).Descendants<Cell>())
                {
                    // Verify if other cells in the document reference the item.
                    if (cell.DataType != null &&
                        cell.DataType.Value == CellValues.SharedString &&
                        cell.CellValue.Text == shareStringId.ToString())
                    {
                        // Other cells in the document still reference the item.
                        // Do not remove the item.
                        remove = false;
                        break;
                    }
                }

                if (!remove)
                {
                    break;
                }
            }

            #endregion

            #region if not items reference

            if (remove)
            {
                SharedStringTablePart shareStringTablePart = _xmlworkbook.WorkbookPart.SharedStringTablePart;
                if (shareStringTablePart == null)
                {
                    return;
                }

                SharedStringItem item =
                    shareStringTablePart.SharedStringTable.Elements<SharedStringItem>().ElementAt(shareStringId);

                if (item == null)
                {
                    return;
                }
                item.Remove();

                // Refresh all the shared string references.
                foreach (var part in _xmlworkbook.WorkbookPart.GetPartsOfType<WorksheetPart>())
                {
                    foreach (var cell in GetSheetData(part).Descendants<Cell>())
                    {
                        if (cell.DataType != null &&
                            cell.DataType.Value == CellValues.SharedString)
                        {
                            int itemIndex = int.Parse(cell.CellValue.Text);
                            if (itemIndex > shareStringId)
                            {
                                cell.CellValue.Text = (itemIndex - 1).ToString();
                            }
                        }
                    }
                    _xmlworkbook.WorkbookPart.Workbook.Save();
                }
                _xmlworkbook.WorkbookPart.SharedStringTablePart.SharedStringTable.Save();
            }

            #endregion
        }

        #endregion

        #region Find xml worksheetpart

        private WorksheetPart GetWorksheetPart(int sheetIndex)
        {
            return GetWorksheetPart(GetXmlSheet(sheetIndex));
        }

        private WorksheetPart GetWorksheetPart(string sheetName)
        {
            return GetWorksheetPart(GetXmlSheet(sheetName));
        }

        private WorksheetPart GetWorksheetPart(Sheet sheet)
        {
            if (sheet == null)
            {
                return null;
            }
            return (WorksheetPart)_xmlworkbook.WorkbookPart.GetPartById(sheet.Id);
        }

        #endregion

        #endregion

        #endregion

        #region Inner enum

        public enum OutputTypeEnum
        {
            CSV,
            Excel,
            Html,
            Xml,
            Default
        }

        public enum CellTypeEnum
        {
            Row,
            Col
        }

        #endregion

        #region Inner Validator helper

        #region Check arguments

        private void CheckArguments(int sheetIndex, int[] rowIndexs, int[] colIndexs)
        {
            if (IsXmlFormat)
            {
                CheckArguments(GetXmlSheet(sheetIndex), rowIndexs, colIndexs);
            }
            CheckArguments(GetSheet(sheetIndex), rowIndexs, colIndexs);
        }

        private void CheckArguments(string sheetName, int[] rowIndexs, int[] colIndexs)
        {
            if (IsXmlFormat)
            {
                CheckArguments(GetXmlSheet(sheetName), rowIndexs, colIndexs);
            }
            CheckArguments(GetSheet(sheetName), rowIndexs, colIndexs);
        }

        private void CheckArguments(int sheetIndex, int rowIndex, int colIndex)
        {
            if (IsXmlFormat)
            {
                CheckArguments(GetXmlSheet(sheetIndex), rowIndex, colIndex);
            }
            CheckArguments(GetSheet(sheetIndex), rowIndex, colIndex);
        }

        private void CheckArguments(string sheetName, int rowIndex, int colIndex)
        {
            if (IsXmlFormat)
            {
                CheckArguments(GetXmlSheet(sheetName), rowIndex, colIndex);
            }
            CheckArguments(GetSheet(sheetName), rowIndex, colIndex);
        }

        private void CheckArguments(int sheetIndex, int rowIndex, int colIndex, bool allowTwoZero)
        {
            if (IsXmlFormat)
            {
                CheckArguments(GetXmlSheet(sheetIndex), rowIndex, colIndex, allowTwoZero);
            }
            CheckArguments(GetSheet(sheetIndex), rowIndex, colIndex, allowTwoZero);
        }

        private void CheckArguments(string sheetName, int rowIndex, int colIndex, bool allowTwoZero)
        {
            if (IsXmlFormat)
            {
                CheckArguments(GetXmlSheet(sheetName), rowIndex, colIndex, allowTwoZero);
            }
            CheckArguments(GetSheet(sheetName), rowIndex, colIndex, allowTwoZero);
        }

        #region Excel

        private void CheckArguments(_Worksheet sheet, int[] rowIndexs, int[] colIndexs)
        {
            int rowIndex = 0;
            int colIndex = 0;
            if (rowIndexs != null && rowIndexs.Count() > 0)
            {
                rowIndexs.OrderByDescending(c => c);
                rowIndex = rowIndexs[0];
            }
            if (colIndexs != null && colIndexs.Count() > 0)
            {
                colIndexs.OrderByDescending(c => c);
                colIndex = colIndexs[0];
            }
            CheckArguments(sheet, rowIndex, colIndex);
        }

        private void CheckArguments(_Worksheet sheet, int rowIndex, int colIndex, bool allowTwoZero)
        {
            if (sheet == null)
            {
                throw new ArgumentNullException();
            }
            if ((rowIndex != 0 && rowIndex > sheet.Rows.Count) ||
                (colIndex != 0 && colIndex > sheet.Columns.Count) ||
                (!allowTwoZero && rowIndex == 0 && colIndex == 0))
            {
                throw new ArgumentOutOfRangeException();
            }
        }

        private void CheckArguments(_Worksheet sheet, int rowIndex, int colIndex)
        {
            CheckArguments(sheet, rowIndex, colIndex, true);
        }

        #endregion

        #region Xml

        private void CheckArguments(Sheet sheet, int[] rowIndexs, int[] colIndexs)
        {
            int rowIndex = 0;
            int colIndex = 0;
            if (rowIndexs != null && rowIndexs.Count() > 0)
            {
                rowIndexs.OrderByDescending(c => c);
                rowIndex = rowIndexs[0];
            }
            if (colIndexs != null && colIndexs.Count() > 0)
            {
                colIndexs.OrderByDescending(c => c);
                colIndex = colIndexs[0];
            }
            CheckArguments(sheet, rowIndex, colIndex);
        }

        private void CheckArguments(Sheet sheet, int rowIndex, int colIndex, bool allowTwoZero)
        {
            if (sheet == null)
            {
                throw new ArgumentNullException();
            }
            //colIndex != 0 && colIndex > GetSheetColCount(sheet))
            if ((rowIndex != 0 && rowIndex > GetSheetRowCount(sheet)) ||
                (!allowTwoZero && rowIndex == 0 && colIndex == 0))
            {
                throw new ArgumentOutOfRangeException();
            }
        }

        private void CheckArguments(Sheet sheet, int rowIndex, int colIndex)
        {
            CheckArguments(sheet, rowIndex, colIndex, true);
        }

        #endregion

        #endregion

        #region Index Exist

        public bool IsRowExist(int sheetIndex, int rowIndex)
        {
            if (IsXmlFormat)
            {
                return rowIndex <= GetSheetRowCount(sheetIndex);
            }
            return IsSheetExist(sheetIndex) && rowIndex <= GetSheet(sheetIndex).Rows.Count;
        }

        public bool IsColExist(int sheetIndex, int colIndex)
        {
            if (IsXmlFormat)
            {
                return colIndex <= GetSheetColCount(sheetIndex);
            }
            return IsSheetExist(sheetIndex) && colIndex <= GetSheet(sheetIndex).Columns.Count;
        }

        public bool IsSheetExist(string sheetName)
        {
            if (IsXmlFormat)
            {
                return GetXmlSheet(sheetName) != null;
            }
            return GetSheet(sheetName) != null;
        }

        public bool IsSheetExist(int sheetIndex)
        {
            if (IsXmlFormat)
            {
                return GetXmlSheet(sheetIndex) != null;
            }
            return sheetIndex <= WorkSheetCount && GetSheet(sheetIndex) != null;
        }

        #endregion

        #region File

        public static bool IsExcelFileExist(string file)
        {
            if (string.IsNullOrEmpty(file))
            {
                return false;
            }
            return File.Exists(file);

        }

        public static void DeleteFile(string file)
        {
            try
            {
                File.Delete(file);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #endregion

        #region Convert

        public static string ConvertToCellId(int colIndex)
        {
            colIndex--;
            if (colIndex < 26)
            {
                return ((char)(colIndex + 'A')).ToString();
            }
            return ((char)(colIndex / 26 - 1 + 'A'))
                   + ((char)(colIndex % 26 + 'A')).ToString();
        }

        public static string ConvertToCellId(int colIndex, int rowIndex)
        {
            return ConvertToCellId(colIndex) + rowIndex;
        }

        #endregion
    }
}
